package cn.pengpeng.sql;

public class SaleJoinSelf {
/**
 * 一：计算出每个月的销售额
select shopid,substr(day,1,7) as month,sum(sale) as month_sale
from t_sale
group by shopid,substr(day,1,7);
 *
二：自关联
select t1.*,t2.* from 
(select shopid,substr(day,1,7) as month,sum(sale) as month_sale
from t_sale
group by shopid,substr(day,1,7)) t1
join 
(select shopid,substr(day,1,7) as month,sum(sale) as month_sale
from t_sale
group by shopid,substr(day,1,7)) t2
on t1.shopid=t2.shopid;
 * 
 * 
三：过滤出来相对应的数据

select t1.*,t2.* from 
(select shopid,substr(day,1,7) as month,sum(sale) as month_sale
from t_sale
group by shopid,substr(day,1,7)) t1
join 
(select shopid,substr(day,1,7) as month,sum(sale) as month_sale
from t_sale
group by shopid,substr(day,1,7)) t2
on t1.shopid=t2.shopid where t1.month>=t2.month;


四：分组统计求和
select t1.shopid,t1.month,sum(t2.month_sale) from 
(select shopid,substr(day,1,7) as month,sum(sale) as month_sale
from t_sale
group by shopid,substr(day,1,7)) t1
join 
(select shopid,substr(day,1,7) as month,sum(sale) as month_sale
from t_sale
group by shopid,substr(day,1,7)) t2
on t1.shopid=t2.shopid 
where t1.month>=t2.month
group by t1.shopid,t1.month



使用窗口函数分析

select shopid,month,month_sale,
sum(month_sale) over(partition by shopid order by month rows between unbounded preceding and current row) as month_accumulate
from 
(select shopid,substr(day,1,7) as month,sum(sale) as month_sale
from t_sale
group by shopid,substr(day,1,7)) tmp;






;
 * 
 * 
 */

}
